package com.hsnn.medstgmini.settlement.dao.impl;

import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.settlement.dao.SettlementFirstDao;
import com.hsnn.medstgmini.settlement.model.SetSettlement;
import com.hsnn.medstgmini.settlement.model.SetSettlementDetail;
import com.hsnn.medstgmini.settlement.model.SetSettlementfirst;
import com.hsnn.medstgmini.settlement.model.form.SetSettlementForm;
import com.hsnn.medstgmini.settlement.model.form.SetSettlementfirstForm;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.CodeUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseNumber;
import com.hsnn.medstgmini.util.PinYin;

@Repository
public class SettlementFirstDaoImpl implements SettlementFirstDao {
	@Autowired
	private HibernateSqlUtil dao;

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;

	private Session getSession() {
		Session session = sessionFactory.getCurrentSession();
		if (null == session) {
			session = sessionFactory.openSession();
		}
		return session;
	}

	/**
	 * 
	 * @category 保存一级结算单信息（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 下午4:01:27
	 * @param data
	 */
	public void saveSettlementFirst(SetSettlement data) {
		data.setSettlementId(UUID.randomUUID().toString());
		data.setSettlementCode(CodeUtil.getNum());
		dao.add(data);
	}

	/**
	 * 
	 * @category 获取我的结算单（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 上午10:35:33
	 * @param page
	 * @return
	 */
	public Pagination getSettlementFirstDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" SELECT a.settlement_id as settlementId,a.settlement_code as settlementCode,")
				.append(" a.settlement_name as settlementName, a.total_orders as totalOrders,a.total_amount as totalAmount,")
				.append(" DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,c.name as createUser,a.status,a.remarks, ")
				.append(" d.name as submitUser,DATE_FORMAT(a.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime")
				.append(" from set_settlement a")
				.append(" LEFT JOIN sys_user c on c.id=a.create_user")
				.append(" left join sys_user d on d.id=a.submit_user")
				.append(" where a.org_id=:hospId ").append(" and a.status=0 ");
		SetSettlementForm form = (SetSettlementForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("hospId", form.getHospId());
		if (StringUtils.isNotBlank(form.getBeginDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')>=:beginDate");
			params.put("beginDate", form.getBeginDate());
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')<=:endDate");
			params.put("endDate", form.getEndDate());
		}
		if (StringUtils.isNotBlank(form.getSubmitBeginDate())) {
			sql.append(" and DATE_FORMAT(a.submit_datetime,'%Y-%m-%d')>=:submitBeginDate");
			params.put("beginDate", form.getSubmitBeginDate());
		}
		if (StringUtils.isNotBlank(form.getSubmitEndDate())) {
			sql.append(" and DATE_FORMAT(a.submit_datetime,'%Y-%m-%d')<=:submitEndDate");
			params.put("submitEndDate", form.getSubmitEndDate());
		}
		if (StringUtils.isNotBlank(form.getSettlementName())) {
			sql.append(" and a.settlement_name like :settlementName");
			params.put("settlementName", "%" + form.getSettlementName().trim()
					+ "%");
		}
		sql.append(" order by a.create_datetime desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取勾选一级结算单明细列表数据（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 下午1:25:09
	 * @param page
	 * @return
	 */
	public Pagination getAddFirstDetailDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select a.trans_id as transId,c.drug_code as drugCode,c.qualityspec_name as drugName,c.form_name as formName,")
				.append("c.spec_name as specName,c.factor,c.unit_name as unit,c.material_name as materialName,c.prod_compname as compName,d.comp_name delCompName,")
				.append("a.bank_acct_trans_type as bankAcctTransType,a.pro_price as proPrice,a.recp_count as recpCount, a.pro_price * a.recp_count sumPrice")
				.append(" from set_settletdetailinfo a")
				.append(" inner join base_goods_item c on c.goods_code = a.goods_code ")
			//	.append(" left join view_drug b on a.drug_id=b.drug_id")
				.append(" left join std_compinfo d on d.comp_id=a.comp_id")
				.append(" where a.hosp_id=:hospId")
				.append(" and a.is_in_settle_slip=0")
				.append(" and not EXISTS")
				.append(" ( SELECT c.settlement_detail_id FROM set_settlement_detail c JOIN set_settlement d ON c.settlement_id = d.settlement_id AND ( d.status = 0 OR d.status = 1 OR d.status = 2 OR d.status = 4 OR d.status = 6 OR d.status = 7 ) ")
				.append(" where c.bank_acct_trans_info_id = a.trans_id )");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("hospId", map.get("hospId"));// 当前登录医疗机构ID
		// params.put("settlementId", map.get("settlementId"));// 结算单ID
		if (StringUtils.isNotBlank(map.get("drugCode"))) {// 药品编码
			sql.append(" and c.drug_code=:drugCode");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank(map.get("drugName"))) {// 通用名
			if (PinYin.isPinYin(map.get("drugName").trim())) {
				sql.append(" and c.qualityspec_name_spel like :drugName");
				params.put("drugName", "%"
						+ map.get("drugName").trim().toUpperCase() + "%");
			} else {
				sql.append(" and c.qualityspec_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}
		}
		if (StringUtils.isNotBlank(map.get("compName"))) {// 生产企业
			sql.append(" and c.prod_compname like :compName");
			params.put("compName", "%" + map.get("compName").trim() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 根据结算单ID获取结算单信息
	 * @author 邱磊
	 * @date 2015年7月22日 下午2:06:14
	 * @param firstlevelSettlementId
	 * @return
	 */
	public SetSettlementfirst getSetFirstById(String firstlevelSettlementId) {
		return (SetSettlementfirst) dao.getUniquerResult(
				SetSettlementfirst.class, firstlevelSettlementId);
	}

	/**
	 * 
	 * @category 添加结算单明细（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 下午2:28:33
	 * @param page
	 * @return
	 */
	public void addFirstDetail(SetSettlementDetail form, String str,
			SysUser loginUser) {
		str = str.replace("\'", "");
		String[] strs = str.split(",");
		for (String bankAcctTransInfoId : strs) {
			SetSettlementDetail setSettlement = new SetSettlementDetail();
			setSettlement.setSettlementDetailId(UUID.randomUUID().toString());
			setSettlement.setBankAcctTransInfoId(bankAcctTransInfoId);
			setSettlement.setSettlementId(form.getSettlementId());
			setSettlement.setCreateDatetime(new Date());
			setSettlement.setCreateUser(form.getCreateUser());
			getSession().save(setSettlement);
		}
		getSession().flush();
		updateAmounts(form.getSettlementId(), loginUser);
	}

	/**
	 * @category 更新一级结算单条目数和总金额
	 * @date 2015年8月8日
	 */
	private void updateAmounts(String settlementId, SysUser loginUser) {
		String sql = "UPDATE set_settlement a JOIN ( SELECT a.sumCount, CASE WHEN a.sumPrice IS NULL THEN 0 ELSE a.sumPrice END sumPrice, a.settlement_id FROM ( SELECT COUNT(*) sumCount, SUM( CASE WHEN b.bank_acct_trans_type = 2 THEN 0 - b.pro_price * b.recp_count ELSE b.pro_price * b.recp_count END ) sumPrice, :settlementId settlement_id FROM set_settlement_detail a JOIN set_settletdetailinfo b ON a.bank_acct_trans_info_id = b.trans_id WHERE a.settlement_id = :settlementId ) a ) b ON a.settlement_id = b.settlement_id SET a.total_amount = b.sumPrice, a.total_orders = b.sumCount, a.upd_user = :updUser, a.upd_datetime = :updDateTime WHERE a.settlement_id = :settlementId ";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setString("settlementId", settlementId);
		query.setString("updUser", loginUser.getId());
		query.setDate("updDateTime", new Date());
		query.executeUpdate();
		getSession().flush();
	}

	/**
	 * 
	 * @category 根据支付明细ID串查询已加入结算单的明细
	 * @author 邱磊
	 * @date 2015年7月22日 下午2:37:50
	 * @param ids
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SetSettlementDetail> getHaveSetFirstDetailList(
			String settlementId, String ids) {
		String hql = "from SetSettlementDetail where bankAcctTransInfoId in (:ids) and settlementId=:settlementId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("ids", ids);
		params.put("settlementId", settlementId);
		return (List<SetSettlementDetail>) dao.getList(hql, params);
	}

	/**
	 * 
	 * @category 编辑保存一级结算单信息（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 下午4:32:32
	 * @param data
	 */
	public void editSettlementFirst(SetSettlement data) {
		dao.update(data);
	}

	/**
	 * 
	 * @category 删除一级结算单信息（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 下午4:42:29
	 * @param settlementId
	 */
	public void deleteSettlementFirst(List<String> ids) {
		for (String id : ids) {
			String sql = "delete from set_settlement where settlement_id = :id ";
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("id", id);
			dao.executeSql(sql, params);
			sql = "delete from set_settlement_detail where settlement_id = :id ";
			dao.executeSql(sql, params);
		}
	}

	/**
	 * 
	 * @category 根据一级结算单ID串查询已提交的一级结算单
	 * @author 邱磊
	 * @date 2015年7月22日 下午7:21:24
	 * @param ids
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SetSettlement> getHaveSetFirstList(List<String> ids) {
		Session session = getSession();
		Criteria criteria = session.createCriteria(SetSettlement.class);
		criteria.add(Restrictions.in("settlementId", ids));
		criteria.add(Restrictions.not(Restrictions.eq("status", 0)));
		return (List<SetSettlement>) criteria.list();
	}

	/**
	 * 
	 * @category 获取某个结算单应支付金额
	 * @author 邱磊
	 * @date 2015年7月22日 下午8:47:08
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Double getSumSetFirstMoney(String id) {
		StringBuffer sql = new StringBuffer()
				.append("select sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end)")
				.append(" from set_settlement_detail a ")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" where a.settlement_id=:id");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", id);
		List<Object> list = (List<Object>) dao.getSqlList(sql.toString(),
				params);
		if (null != list && list.size() > 0) {
			Object obj = list.get(0);
			if (obj == null) {
				return null;
			}
			return ParseNumber.toDouble(obj.toString());
		} else {
			return null;
		}
	}

	/**
	 * 
	 * @category 获取生成结算单后的可用余额
	 * @author 邱磊
	 * @date 2015年7月23日 上午9:16:37
	 * @param ids
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Double getAvailableAmt(List<String> ids) {
		StringBuffer sql = new StringBuffer()
				.append(" select (d.available_amt - sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end))")
				.append(" from set_settlement_detail a ")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" left join set_settlement c on c.settlement_id=a.settlement_id")
				.append(" left join set_acctinfo d on d.org_id=c.org_id")
				.append(" where a.settlement_id in (:ids)")
				.append(" group by d.org_id,d.available_amt");
		
		SQLQuery query = getSession().createSQLQuery(sql.toString());
		query.setParameterList("ids", ids);
		
		List<Object> list = (List<Object>) query.list();
		if (null != list && list.size() > 0) {
			Object obj = list.get(0);
			return ParseNumber.toDouble(obj.toString());
		} else {
			return null;
		}
	}

	/**
	 * 
	 * @category 提交一级结算单
	 * @author 邱磊
	 * @date 2015年7月22日 下午7:26:05
	 * @param form
	 * @param str
	 */
	public void subSettlementFirst(SetSettlement form, List<String> ids) {
		
		//	更新结算单状态
		StringBuffer sql = new StringBuffer().append("update set_settlement ")
				.append(" set submit_user=:submitUser,")
				.append(" submit_datetime=SYSDATE(),").append(" status=1")
				.append(" where settlement_id in (:ids)");
		
		SQLQuery query = getSession().createSQLQuery(sql.toString());
		query.setString("submitUser", form.getSubmitUser());
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();
		
		//	更新支付明细为已纳入结算单
		sql = new StringBuffer()
				.append("update set_settletdetailinfo a,set_settlement_detail b")
				.append(" set is_in_settle_slip=1,")
				.append(" add_settle_slip_dttm=SYSDATE()")
				.append(" where a.trans_id=b.bank_acct_trans_info_id")
				.append(" and b.settlement_id in (:ids)");
		
		query = getSession().createSQLQuery(sql.toString());
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();
		
		//	更新账户可能余额和待结算单金额
		sql = new StringBuffer()
				.append("update set_acctinfo o, (select c.org_id,sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as totalMoney")
				.append(" from set_settlement_detail a ")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" left join set_settlement c on c.settlement_id=a.settlement_id")
				.append(" where a.settlement_id in (:ids) group by c.org_id) p")
				.append(" set o.available_amt=o.available_amt-p.totalMoney, o.pending_amt=o.pending_amt-p.totalMoney, o.available_amt_upd_dttm=sysdate(), o.pending_amt_upd_dttm=sysdate() ")
				.append(" where o.org_id=p.org_id");
		
		query = getSession().createSQLQuery(sql.toString());
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();
	}

	/**
	 * 
	 * @category 获取结算单明细（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 下午7:54:36
	 * @param page
	 * @return
	 */
	public Pagination getSetSettlemenDetailDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" select settlement_detail_id as settlementDetailId,e.name as createUser,")
				.append(" DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,b.bank_acct_trans_type as bankAcctTransType,b.comp_id as delCompId,")
				.append(" d.comp_name as delCompName,c.drug_code as drugCode,c.qualityspec_name as drugName,c.form_name as formName,c.material_name as materialName,")
				.append(" c.spec_name as specName,c.factor,c.unit_name as unit,c.prod_compname as compName,b.pro_price as proPrice,b.recp_count as recpCount, b.pro_price * b.recp_count sumPrice")
				.append(" from set_settlement_detail a ")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id ")
				.append(" inner join base_goods_item c on c.goods_code = b.goods_code ")
				.append(" left join std_compinfo d on d.comp_id=b.comp_id ")
				.append(" left join sys_user e on e.id=a.create_user ")
				.append(" where a.settlement_id=:settlementId ");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("settlementId", map.get("settlementId"));
		if (StringUtils.isNotBlank(map.get("drugCode"))) {// 药品编码
			sql.append(" and c.drug_code=:drugCode");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank(map.get("drugName"))) {// 通用名
			if (PinYin.isPinYin(map.get("drugName").trim())) {
				sql.append(" and c.drug_name_spel like :drugName");
				params.put("drugName", "%"
						+ map.get("drugName").trim().toUpperCase() + "%");
			} else {
				sql.append(" and c.drug_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}
		}
		if (StringUtils.isNotBlank(map.get("compName"))) {// 生产企业
			sql.append(" and c.comp_name like :compName");
			params.put("compName", "%" + map.get("compName").trim() + "%");
		}
		if (StringUtils.isNotBlank(map.get("bankAcctTransType"))) {// 支付类型
			sql.append(" and b.bank_acct_trans_type =:bankAcctTransType");
			params.put("bankAcctTransType", map.get("bankAcctTransType"));
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取结算单明细（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 下午7:54:36
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getSetSettlemenDetailList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT d.settlement_detail_id AS settlementDetailId,t.settlement_id AS settlementId,t.settlement_name AS settlementName,  ")
				.append("t.total_orders AS totalOrders,t.settlement_code AS settlementCode, c.hosp_code AS hospCode,c.hosp_name AS hospName,  ")
				.append("t.total_amount AS totalAmount,DATE_FORMAT(t.submit_datetime,'%Y-%m-%d %H:%i:%S') AS submitDatetime,a.name AS submitUser,t.remarks  ")
				.append("FROM  set_settlement_detail d   ")
				.append("LEFT JOIN set_settlement t ON d.bank_acct_trans_info_id=t.settlement_id  ")
				.append("LEFT JOIN sys_user a ON t.submit_user=a.id  ")
				.append("LEFT JOIN std_hospinfo c ON c.hosp_id=t.org_id  ")
				.append("WHERE t.settlement_type=1 AND d.settlement_id=:settlementId ");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("settlementId", map.get("settlementId"));// 结算单ID
		if (StringUtils.isNotBlank(map.get("hospCode"))) {// 医疗机构编码
			sql.append(" and c.hosp_code=:hospCode");
			params.put("hospCode", map.get("hospCode"));
		}
		if (StringUtils.isNotBlank(map.get("hospName"))) {// 医疗机构名称
			sql.append(" and c.hosp_name like :hospName");
			params.put("hospName", "%" + map.get("hospName").trim() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 删除明细（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月22日 下午8:25:59
	 * @param page
	 */
	public void deleteSetSettlemenFirstDetail(String settlementId,
			List<String> ids, SysUser loginUser) {
		String sql = "DELETE from set_settlement_detail where settlement_detail_id in (:ids)";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();
		updateAmounts(settlementId, loginUser);
	}

	/**
	 * 
	 * @category 删除明细（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 下午8:25:59
	 * @param page
	 */
	@Override
	public void delSetSettlemenFirstDetail(String settlementId,
			List<String> ids, SysUser loginUser) {
		String sql = "DELETE from set_settlement_detail where settlement_detail_id in (:ids)";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();

		updateSecondAmounts(settlementId, loginUser);
	}

	/**
	 * @category 更新二级结算单条目数和总金额
	 * @date 2015年8月8日
	 */
	private void updateSecondAmounts(String settlementId, SysUser loginUser) {
		String sql = "UPDATE set_settlement a JOIN ( SELECT a.sumCount, CASE WHEN a.sumPrice IS NULL THEN 0 ELSE a.sumPrice END sumPrice, a.settlement_id FROM ( SELECT COUNT(*) sumCount, SUM(b.total_amount) sumPrice, :settlementId settlement_id FROM set_settlement_detail a JOIN set_settlement b ON a.bank_acct_trans_info_id = b.settlement_id WHERE a.settlement_id = :settlementId ) a ) b ON a.settlement_id = b.settlement_id SET a.total_orders = b.sumCount, a.total_amount = b.sumPrice, a.upd_user = :updUser, a.upd_datetime = :updDateTime WHERE a.settlement_id = :settlementId ";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setString("settlementId", settlementId);
		query.setString("updUser", loginUser.getId());
		query.setDate("updDateTime", new Date());
		query.executeUpdate();
		getSession().flush();
	}

	/**
	 * 
	 * @category 获取一级结算单第一次审核数据（核算中心）
	 * @author 邱磊
	 * @date 2015年7月23日 上午11:03:49
	 * @param page
	 * @return
	 */
	public Pagination getAuditFirstOneDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select settlement_id as settlementId,a.settlement_code as settlementCode,a.settlement_name as settlementName,")
				.append("c.hosp_code as hospCode,c.hosp_name as hospName,e.area_name as areaName,a.total_orders as totalOrders,")
				.append("a.total_amount as totalAmount,DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,f.name as createUser,d.name as submitUser,")

				.append("DATE_FORMAT(a.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,a.status,DATE_FORMAT(a.audit_datetime,'%Y-%m-%d %H:%i:%S') as auditDatetime,")
				.append("g.name as auditUser,DATE_FORMAT(a.reaudit_datetime,'%Y-%m-%d %H:%i:%S') as reauditDatetime,h.name as reauditUser")
				.append(" from set_settlement a")
				.append(" left join set_relationship b on b.org_id=a.org_id")
				.append(" left join std_hospinfo c on c.hosp_id=a.org_id")
				.append(" left join sys_user d on a.submit_user=d.id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join sys_user f on f.id=a.create_user")
				.append(" left join sys_user g on g.id=a.audit_user")
				.append(" left join sys_user h on h.id=a.reaudit_user")
				.append(" where b.father_org_id=:heaBurId and a.settlement_type=1 and a.status=1");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("heaBurId", page.getConditions().get("heaBurId"));
		SetSettlementForm form = (SetSettlementForm) page.getForm();
		if (page.getForm() != null) {
			if (StringUtils.isNotBlank(form.getBeginDate())) {
				sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')>=:beginDate");
				params.put("beginDate", form.getBeginDate());
			}
			if (StringUtils.isNotBlank(form.getEndDate())) {
				sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')<=:endDate");
				params.put("endDate", form.getEndDate());
			}
			if (StringUtils.isNotBlank(form.getStatus())) {
				sql.append(" and a.status=:status");
				params.put("status", form.getStatus());
			}
			if (StringUtils.isNotBlank(form.getHospCode())) {
				sql.append(" and c.hosp_code=:hospCode");
				params.put("getHospCode", form.getHospCode());
			}
			if (StringUtils.isNotBlank(form.getAreaCode())) {
				sql.append(" and e.area_id=:areaCode");
				params.put("areaCode", form.getAreaCode());
			}
			if (StringUtils.isNotBlank(form.getAreaFatherCode())) {
				sql.append(" and e.father_id=:areaFatherCode");
				params.put("areaFatherCode", form.getAreaFatherCode());
			}
			if (StringUtils.isNotBlank(form.getHospName())) {
				sql.append(" and c.hosp_name like :hospName");
				params.put("hospName", "%" + form.getHospName().trim() + "%");
			}
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取一级结算单第二次审核数据（核算中心）
	 * @author 邱磊
	 * @date 2015年7月23日 上午11:03:49
	 * @param page
	 * @return
	 */
	public Pagination getAuditFirstSecondDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" select settlement_id as settlementId,a.settlement_code as settlementCode,a.settlement_name as settlementName,")
				.append(" c.hosp_code as hospCode,c.hosp_name as hospName,e.area_name as areaName,a.total_orders as totalOrders,")
				.append(" a.total_amount as totalAmount,DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,f.name as createUser,d.name as submitUser,")
				.append(" DATE_FORMAT(a.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,a.status,DATE_FORMAT(a.audit_datetime,'%Y-%m-%d %H:%i:%S') as auditDatetime,")
				.append(" g.name as auditUser,DATE_FORMAT(a.reaudit_datetime,'%Y-%m-%d %H:%i:%S') as reauditDatetime,h.name as reauditUser")
				.append(" from set_settlement a")
				.append(" left join set_relationship b on b.org_id=a.org_id")
				.append(" left join std_hospinfo c on c.hosp_id=a.org_id")
				.append(" left join sys_user d on a.submit_user=d.id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join sys_user f on f.id=a.create_user")
				.append(" left join sys_user g on g.id=a.audit_user")
				.append(" left join sys_user h on h.id=a.reaudit_user")
				.append(" where b.father_org_id=:heaBurId and a.settlement_type=1 and a.status=2 ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("heaBurId", page.getConditions().get("heaBurId"));
		SetSettlementForm form = (SetSettlementForm) page.getForm();
		if (page.getForm() != null) {
			if (StringUtils.isNotBlank(form.getBeginDate())) {
				sql.append(" and DATE_FORMAT(a.submit_datetime,'%Y-%m-%d')>=:beginDate");
				params.put("beginDate", form.getBeginDate());
			}
			if (StringUtils.isNotBlank(form.getEndDate())) {
				sql.append(" and DATE_FORMAT(a.submit_datetime,'%Y-%m-%d')<=:endDate");
				params.put("endDate", form.getEndDate());
			}
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取二级结算单第一次审核数据（采购中心）
	 * @author 邱磊
	 * @date 2015年7月23日 上午11:03:49
	 * @param page
	 * @return
	 */
	public Pagination getAuditSecondOneDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select settlement_id as settlementId,a.settlement_code as settlementCode,a.settlement_name as settlementName,")
				.append("c.hea_bur_code as heaBurCode,c.hea_bur_name as heaBurName,e.area_name as areaName,a.total_orders as totalOrders,")
				.append("a.total_amount as totalAmount,DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,f.name as createUser,d.name as submitUser,")
				.append("DATE_FORMAT(a.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,a.status,DATE_FORMAT(a.audit_datetime,'%Y-%m-%d %H:%i:%S') as auditDatetime,")
				.append("g.name as auditUser,DATE_FORMAT(a.reaudit_datetime,'%Y-%m-%d %H:%i:%S') as reauditDatetime,h.name as reauditUser")
				.append(" from set_settlement a")
				.append(" left join std_healthbureau c on c.hea_bur_id=a.org_id")
				.append(" left join sys_user d on a.submit_user=d.id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join sys_user f on f.id=a.create_user")
				.append(" left join sys_user g on g.id=a.audit_user")
				.append(" left join sys_user h on h.id=a.reaudit_user")
				.append(" where a.settlement_type=2 and a.status=1");
		SetSettlementfirstForm form = (SetSettlementfirstForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fatherOrgId", form.getHealBurId());
		if (StringUtils.isNotBlank(form.getBeginDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')>=:beginDate");
			params.put("beginDate", form.getBeginDate());
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')<=:endDate");
			params.put("endDate", form.getEndDate());
		}
		if (StringUtils.isNotBlank(form.getStatus())) {
			sql.append(" and a.status=:status");
			params.put("status", form.getStatus());
		}
		if (StringUtils.isNotBlank(form.getHealBurCode())) {
			sql.append(" and c.hea_bur_code=:healBurCode");
			params.put("healBurCode", form.getHealBurCode());
		}
		if (StringUtils.isNotBlank(form.getAreaCode())) {
			sql.append(" and e.area_id=:areaCode");
			params.put("areaCode", form.getAreaCode());
		}
		if (StringUtils.isNotBlank(form.getAreaFatherCode())) {
			sql.append(" and e.father_id=:areaFatherCode");
			params.put("areaFatherCode", form.getAreaFatherCode());
		}
		if (StringUtils.isNotBlank(form.getHealBurName())) {
			sql.append(" and c.hea_bur_name like :healBurName");
			params.put("healBurName", "%" + form.getHealBurName().trim() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), null, page);
	}

	/**
	 * 
	 * @category 获取二级结算单第二次审核数据（采购中心）
	 * @author 邱磊
	 * @date 2015年7月23日 上午11:03:49
	 * @param page
	 * @return
	 */
	public Pagination getAuditSecondSecondDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select settlement_id as settlementId,a.settlement_code as settlementCode,a.settlement_name as settlementName,")
				.append("c.hea_bur_code as heaBurCode,c.hea_bur_name as heaBurName,e.area_name as areaName,a.total_orders as totalOrders,")
				.append("a.total_amount as totalAmount,DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,f.name as createUser,d.name as submitUser,")
				.append("DATE_FORMAT(a.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,a.status,DATE_FORMAT(a.audit_datetime,'%Y-%m-%d %H:%i:%S') as auditDatetime,")
				.append("g.name as auditUser,DATE_FORMAT(a.reaudit_datetime,'%Y-%m-%d %H:%i:%S') as reauditDatetime,h.name as reauditUser")
				.append(" from set_settlement a")
				.append(" left join std_healthbureau c on c.hea_bur_id=a.org_id")
				.append(" left join sys_user d on a.submit_user=d.id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join sys_user f on f.id=a.create_user")
				.append(" left join sys_user g on g.id=a.audit_user")
				.append(" left join sys_user h on h.id=a.reaudit_user")
				.append(" where a.settlement_type=2 and a.status=2");
		SetSettlementfirstForm form = (SetSettlementfirstForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		// params.put("fatherOrgId", form.getHealBurId());
		if (StringUtils.isNotBlank(form.getBeginDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')>=:beginDate");
			params.put("beginDate", form.getBeginDate());
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')<=:endDate");
			params.put("endDate", form.getEndDate());
		}
		if (StringUtils.isNotBlank(form.getStatus())) {
			sql.append(" and a.status=:status");
			params.put("status", form.getStatus());
		}
		if (StringUtils.isNotBlank(form.getHealBurCode())) {
			sql.append(" and c.hea_bur_code=:healBurCode");
			params.put("healBurCode", form.getHealBurCode());
		}
		if (StringUtils.isNotBlank(form.getAreaCode())) {
			sql.append(" and e.area_id=:areaCode");
			params.put("areaCode", form.getAreaCode());
		}
		if (StringUtils.isNotBlank(form.getAreaFatherCode())) {
			sql.append(" and e.father_id=:areaFatherCode");
			params.put("areaFatherCode", form.getAreaFatherCode());
		}
		if (StringUtils.isNotBlank(form.getHealBurName())) {
			sql.append(" and c.hea_bur_name like :healBurName");
			params.put("healBurName", "%" + form.getHealBurName().trim() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), null, page);
	}

	/**
	 * 
	 * @category 根据结算单ID获取结算单信息(新表)
	 * @author 邱磊
	 * @date 2015年7月22日 下午2:06:14
	 * @param firstlevelSettlementId
	 * @return
	 */
	public SetSettlement getSetSettlementById(String settlementId) {
		return (SetSettlement) dao.getUniquerResult(SetSettlement.class,
				settlementId);
	}

	/**
	 * 
	 * @category 审核一级结算单第一次（核算中心）
	 * @author 邱磊
	 * @date 2015年7月23日 下午4:45:21
	 * @param form
	 */
	public void auditFirstOne(SetSettlementForm form) {
		StringBuffer sql = new StringBuffer().append("update set_settlement")
				.append(" set audit_datetime=SYSDATE(),")
				.append(" audit_user=:auditUser,")
				.append(" audit_remark=:auditRemark,")
				.append(" status=:status")
				.append(" where settlement_id=:settlementId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("auditUser", form.getAuditUser());
		params.put("auditRemark", form.getAuditRemark());
		params.put("settlementId", form.getSettlementId());
		params.put("status", form.getStatus());
		dao.executeSql(sql.toString(), params);
		if ("3".equals(form.getStatus())) {
			returnInStatus(form.getSettlementId());
			addAcctinfoMoney(form.getSettlementId());
		}
		getSession().flush();
	}

	/**
	 * 
	 * @category 审核一级结算单第二次（核算中心）
	 * @author 邱磊
	 * @date 2015年7月23日 下午4:45:21
	 * @param form
	 */
	public void auditFirstSecond(SetSettlement form) {
		StringBuffer sql = new StringBuffer().append("update set_settlement")
				.append(" set reaudit_datetime=SYSDATE(),")
				.append(" reaudit_user=:reauditUser,")
				.append(" reaudit_remark=:reauditRemark,")
				.append(" status=:status")
				.append(" where settlement_id=:settlementId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("reauditUser", form.getAuditUser());
		params.put("reauditRemark", form.getAuditRemark());
		params.put("settlementId", form.getSettlementId());
		params.put("status", form.getStatus());
		dao.executeSql(sql.toString(), params);
		if (5 == form.getStatus()) {
			returnInStatus(form.getSettlementId());
			addAcctinfoMoney(form.getSettlementId());
		}
		getSession().flush();
	}

	/**
	 * 
	 * @category 审核一级结算单第二次（采购中心）
	 * @author 邱磊
	 * @date 2015年7月23日 下午4:45:21
	 * @param form
	 */
	public void centerAuditFirstSecond(SetSettlement form) {
		StringBuffer sql = new StringBuffer().append("update set_settlement")
				.append(" set reaudit_datetime=SYSDATE(),")
				.append(" reaudit_user=:reauditUser,")
				.append(" reaudit_remark=:reauditRemark,")
				.append(" pay_status=1,").append(" status=:status")
				.append(" where settlement_id=:settlementId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("reauditUser", form.getAuditUser());
		params.put("reauditRemark", form.getAuditRemark());
		params.put("settlementId", form.getSettlementId());
		params.put("status", form.getStatus());
		dao.executeSql(sql.toString(), params);
		if (5 == form.getStatus()) {
			returnInStatus(form.getSettlementId());
		}
	}

	/**
	 * 
	 * @category 保存二级结算单信息
	 * @author 邱磊
	 * @date 2015年7月23日 下午7:14:37
	 * @param data
	 */
	public void saveSettlementSecond(SetSettlement data) {
		data.setSettlementId(UUID.randomUUID().toString());
		data.setSettlementCode(CodeUtil.getNum());
		dao.add(data);
	}

	/**
	 * 
	 * @category 获取一级结算单审核信息
	 * @author 邱磊
	 * @date 2015年7月23日 下午5:41:27
	 * @param settlementId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getSetFirstAuditInfo(String settlementId) {
		StringBuffer sql = new StringBuffer()
				.append("select t.settlement_id as settlementId,t.settlement_code as settlementCode,t.settlement_name as settlementName,")
				.append("DATE_FORMAT(t.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,t.total_orders as totalOrders,")
				.append("t.total_amount as totalAmount,t.remarks,c.name as createUser,")
				.append("t.status,t.audit_datetime as auditDatetime,a.name as auditUser,t.audit_remark as auditRemark,")
				.append(" t.reaudit_datetime as reauditDatetime,b.name as reauditUser,t.reaudit_remark as reauditRemark,t.status as restatus  ")
				.append(" from set_settlement t")
				.append(" left join sys_user a on a.id=t.audit_user")
				.append(" left join sys_user b on b.id=t.reaudit_user")
				.append(" left join sys_user c on c.id=t.create_user")
				.append(" where settlement_id=:settlementId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("settlementId", settlementId);
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(),
				params);

	}

	/**
	 * 
	 * @category 获取二级结算单（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 上午10:35:33
	 * @param page
	 * @return
	 */
	public Pagination getSettlementSecondDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" SELECT a.settlement_id as settlementId,a.settlement_code as settlementCode,")
				.append(" a.settlement_name as settlementName,b.hosp_name as hospName, a.total_orders as totalOrders,a.total_amount as totalAmount,")
				.append(" DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,c.name as createUser,a.status,a.remarks ")
				.append(" from set_settlement a")
				.append(" left join std_hospinfo b on b.hosp_id=a.org_id")
				.append(" LEFT JOIN sys_user c on c.id=a.create_user")
				.append(" left join set_relationship d on d.org_id=a.org_id")
				.append(" where a.settlement_type=2 and a.status=0 ")
				.append(" and a.org_id=:fatherOrgId ");
		SetSettlementfirstForm form = (SetSettlementfirstForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fatherOrgId", form.getHealBurId());
		if (StringUtils.isNotBlank(form.getBeginDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')>=:beginDate");
			params.put("beginDate", form.getBeginDate());
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')<=:endDate");
			params.put("endDate", form.getEndDate());
		}
		sql.append(" order by a.create_datetime desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取我的结算单查看列表（核算中心）
	 * @author 徐国鹏
	 * @date 2015年7月22日 上午10:35:33
	 * @param page
	 * @return
	 */
	public Pagination getSettlementSecondDataListSee(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT a.settlement_id as settlementId,a.settlement_code as settlementCode,")
				.append(" DATE_FORMAT(a.submit_datetime,'%Y-%m-%d  %H:%i:%S') as submitDatetime, su.name as submitUser,")
				.append(" b.hosp_code as hospCode,")
				.append(" a.settlement_name as settlementName,b.hosp_name as hospName, a.total_orders as totalOrders,a.total_amount as totalAmount,")
				.append(" DATE_FORMAT(a.create_datetime,'%Y-%m-%d  %H:%i:%S') as createDatetime,c.name as createUser,a.status,a.remarks ")
				.append(" from set_settlement a")
				.append(" left join std_hospinfo b on b.hosp_id=a.org_id")
				.append(" LEFT JOIN sys_user su on su.id=a.submit_user")
				.append(" LEFT JOIN sys_user c on c.id=a.create_user")
				.append(" left join set_relationship d on d.org_id=a.org_id")
				.append(" where a.settlement_type=2  ")
				.append(" and a.org_id=:fatherOrgId");
		SetSettlementfirstForm form = (SetSettlementfirstForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fatherOrgId", form.getHealBurId());
		if (StringUtils.isNotBlank(form.getBeginDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')>=:beginDate");
			params.put("beginDate", form.getBeginDate());
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')<=:endDate");
			params.put("endDate", form.getEndDate());
		}
		if (StringUtils.isNotBlank(form.getSubmitDatetime())) {
			sql.append(" and DATE_FORMAT(a.submit_datetime,'%Y-%m-%d')>=:submitDatetime");
			params.put("submitDatetime", form.getSubmitDatetime());
		}
		/*
		 * if (StringUtils.isNotBlank(form.getStatus())) {
		 * sql.append(" and a.status=:status"); params.put("status",
		 * form.getStatus()); }
		 */return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取可加入二级结算单明细（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 下午1:25:09
	 * @param page
	 * @return
	 */
	public Pagination getAddSecondDetailDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT t.settlement_id as settlementId,t.settlement_name as settlementName,t.total_orders as totalOrders,t.settlement_code as settlementCode, ")
				.append(" c.hosp_code as hospCode,c.hosp_name as hospName,")
				.append(" t.total_amount as totalAmount,DATE_FORMAT(t.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,a.name as submitUser,t.remarks")
				.append(" from set_settlement t")
				.append(" left join sys_user a on t.submit_user=a.id")
				.append(" left join set_relationship b on b.org_id=t.org_id")
				.append(" left join std_hospinfo c on c.hosp_id=t.org_id")
				.append(" where t.settlement_type=1 and t.status>=4")
				.append(" and b.father_org_id=:fatherOrgId")
				.append(" and not exists ( SELECT d.settlement_detail_id FROM set_settlement_detail d JOIN set_settlement e ON d.settlement_id = e.settlement_id AND ( e.status = 0 OR e.status = 1 OR e.status = 2 OR e.status = 4 OR e.status = 6 OR e.status = 7 ) ")
				.append(" where d.bank_acct_trans_info_id = t.settlement_id ) ");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fatherOrgId", map.get("heaBurId"));// 当前登录核算中心机构ID
		// params.put("settlementId", map.get("settlementId"));// 结算单ID
		if (StringUtils.isNotBlank(map.get("hospCode"))) {// 医疗机构编码
			sql.append(" and c.hosp_code=:hospCode");
			params.put("hospCode", map.get("hospCode"));
		}
		if (StringUtils.isNotBlank(map.get("hospName"))) {// 医疗机构名称
			sql.append(" and c.hosp_name like :hospName");
			params.put("hospName", "%" + map.get("hospName").trim() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 根据结算单ID获取二级结算单信息
	 * @author 邱磊
	 * @date 2015年7月22日 下午2:06:14
	 * @param settlementId
	 * @return
	 */
	public SetSettlement getSetSecondById(String settlementId) {
		return (SetSettlement) dao.getUniquerResult(SetSettlement.class,
				settlementId);
	}

	/**
	 * 
	 * @category 根据支付明细ID串查询已加入结算单的明细（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 下午2:37:50
	 * @param ids
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SetSettlementDetail> getHaveSetSecondDetailList(
			String settlementId, List<String> ids) {
		Criteria criteria = getSession().createCriteria(
				SetSettlementDetail.class);
		criteria.add(Restrictions.in("bankAcctTransInfoId", ids));
		criteria.add(Restrictions.eq("settlementId", settlementId));
		return (List<SetSettlementDetail>) criteria.list();
	}

	/**
	 * 
	 * @category 添加二级结算单明细（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 下午2:28:33
	 * @param page
	 * @return
	 */
	public void addSecondDetail(SetSettlementDetail form, List<String> ids,
			SysUser loginUser) {
		StringBuffer sql = new StringBuffer()
				.append(" INSERT into set_settlement_detail")
				.append(" select UUID(),'")
				.append(form.getSettlementId())
				.append("',a.settlement_id,SYSDATE(),'")
				.append(form.getCreateUser())
				.append("' from set_settlement a ")
				.append(" where a.settlement_id in (:ids)")
				.append(" and a.settlement_type=1 and a.status>=4 ")
				.append(" and not EXISTS (select b.settlement_detail_id")
				.append(" from set_settlement_detail b")
				.append(" left join set_settlement c on b.settlement_id=c.settlement_id")
				.append(" where b.bank_acct_trans_info_id=a.settlement_id and c.status = 0 and b.settlement_id=:settlementId)");

		SQLQuery query = getSession().createSQLQuery(sql.toString());
		query.setString("settlementId", form.getSettlementId());
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();

		updateSecondAmounts(form.getSettlementId(), loginUser);
	}

	/**
	 * 
	 * @category 根据结算单ID获取结算单信息
	 * @author 邱磊
	 * @date 2015年7月22日 下午2:06:14
	 * @param settlementId
	 * @return
	 */
	public SetSettlement getSettlementById(String settlementId) {
		return (SetSettlement) dao.getUniquerResult(SetSettlement.class,
				settlementId);
	}

	/**
	 * 
	 * @category 编辑保存二级结算单信息（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 下午4:32:32
	 * @param data
	 */
	public void editSettlementSecond(SetSettlement data) {
		dao.update(data);
	}

	/**
	 * 
	 * @category 删除二级结算单信息（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 下午4:42:29
	 * @param settlementId
	 */
	public void deleteSettlementSecond(String settlementId) {
		String sql = "delete from set_settlement where settlement_id=:id";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", settlementId);
		dao.executeSql(sql, params);
		sql = "delete from set_settlement_detail where settlement_id=:id";
		dao.executeSql(sql, params);
	}

	/**
	 * 
	 * @category 根据二级结算单ID串查询已提交的结算单（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 下午7:21:24
	 * @param ids
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SetSettlement> getHaveSetSecondList(List<String> ids) {
		Criteria criteria = getSession().createCriteria(SetSettlement.class);
		criteria.add(Restrictions.in("settlementId", ids));
		criteria.add(Restrictions.not(Restrictions.eq("status", 0)));
		return (List<SetSettlement>) criteria.list();
	}

	/**
	 * 
	 * @category 获取生成结算单后的核算中心账户可用余额（核算中心）
	 * @author 邱磊
	 * @date 2015年7月23日 上午9:16:37
	 * @param ids
	 * @return
	 */
	public Double getSecondAvailableAmt(List<String> ids) {
		String sql = "SELECT MAX(d.available_amt) - SUM(b.total_amount) FROM set_settlement_detail a JOIN set_settlement b ON a.bank_acct_trans_info_id = b.settlement_id JOIN set_settlement c ON a.settlement_id = c.settlement_id JOIN set_acctinfo d ON c.org_id = d.org_id WHERE a.settlement_id IN ( :ids ) ";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setParameterList("ids", ids);
		BigDecimal result = (BigDecimal) query.uniqueResult();
		return result.doubleValue();
	}

	/**
	 * 
	 * @category 提交二级结算单（核算中心）
	 * @author 邱磊
	 * @date 2015年7月22日 下午7:26:05
	 * @param form
	 * @param str
	 */
	public void subSettlementSecond(SetSettlement form, List<String> ids) {

		// 更新结算单状态为已提交
		StringBuffer sql = new StringBuffer().append("update set_settlement ")
				.append(" set submit_user=:submitUser,")
				.append(" submit_datetime=SYSDATE(),").append(" status=1")
				.append(" where settlement_id in (:ids) and status=0");

		SQLQuery query = getSession().createSQLQuery(sql.toString());
		query.setParameter("submitUser", form.getSubmitUser());
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();

		// 更新一级结算单状态为已纳入二级结算单
		sql = new StringBuffer()
				.append("update set_settlement o ,(select a.bank_acct_trans_info_id from set_settlement_detail a")
				.append(" where a.settlement_id in (:ids)) p")
				.append(" set o.status=6")
				.append(" where o.settlement_id=p.bank_acct_trans_info_id");

		query = getSession().createSQLQuery(sql.toString());
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();

		// 更新卫生局账户可用余额和待结算金额
		sql = new StringBuffer()
				.append("update set_acctinfo o, (select c.org_id, sum(b.total_amount) as totalMoney")
				.append(" from set_settlement_detail a ")
				.append(" left join set_settlement b on a.bank_acct_trans_info_id=b.settlement_id")
				.append(" left join set_settlement c on c.settlement_id=a.settlement_id")
				.append(" where a.settlement_id in (:ids)")
				.append(" group by c.org_id) p")
				.append(" set o.available_amt=o.available_amt-p.totalMoney, o.pending_amt=o.pending_amt-p.totalMoney ")
				.append(" , o.available_amt_upd_dttm=sysdate(), o.pending_amt_upd_dttm=sysdate() ")
				.append(" where o.org_id=p.org_id");

		query = getSession().createSQLQuery(sql.toString());
		query.setParameterList("ids", ids);
		query.executeUpdate();
		getSession().flush();
	}

	/**
	 * 
	 * @category 根据结算单Id查询信息
	 * @author 邱磊
	 * @date 2015年7月24日 下午5:33:12
	 * @param settlementId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getSettlementInfoById(String settlementId) {
		StringBuffer sql = new StringBuffer()
				.append("select a.settlement_id as settlementId,a.settlement_code as settlementCode,a.settlement_name as settlementName,")
				.append("a.total_orders as totalOrders,a.total_amount as totalAmount,DATE_FORMAT(a.create_datetime,'%Y-%m-%d') as createDatetime,")
				.append("b.name as createUser,a.remarks,c.name as submitUser,DATE_FORMAT(a.submit_datetime,'%Y-%m-%d')as submitDatetime,")
				.append("a.status,DATE_FORMAT(a.audit_datetime,'%Y-%m-%d')as auditDatetime,d.name as auditUser,a.audit_remark as auditRemark,")
				.append("DATE_FORMAT(a.reaudit_datetime,'%Y-%m-%d')as reauditDatetime,e.name as reauditUser,a.reaudit_remark as reauditRemark,")
				.append("a.settlement_type as settlementType from set_settlement a")
				.append(" left join sys_user b on b.id=a.create_user")
				.append(" left join sys_user c on c.id=a.submit_user")
				.append(" left join sys_user d on d.id=a.audit_user")
				.append(" left join sys_user e on e.id=a.reaudit_user")
				.append(" where a.settlement_id=:settlementId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("settlementId", settlementId);
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao
				.getSqlMapList(sql.toString(), params);
		if (null != list && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * 
	 * @category 审核不能过时改变结算单中支付明细的"纳入结算状态"
	 * @author 邱磊
	 * @date 2015年7月23日 下午5:33:05
	 * @param settlementId
	 *            结算单ID
	 */
	private void returnInStatus(String settlementId) {
		StringBuffer sql = new StringBuffer()
				.append("update set_settletdetailinfo t set is_in_settle_slip=0")
				.append(" where exists (select a.settlement_detail_id from set_settlement_detail a where a.bank_acct_trans_info_id=t.trans_id and a.settlement_id=:settlementId)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("settlementId", settlementId);
		dao.executeSql(sql.toString(), params);
	}

	/**
	 * 
	 * @category 一级结算单提交单成功后扣减账户的可用余额与待结算金额
	 * @author 邱磊
	 * @date 2015年7月25日 上午9:44:43
	 * @param settlementId
	 */
	@SuppressWarnings("unused")
	private void deductionAcctinfoMoney(String settlementId) {
		StringBuffer sql = new StringBuffer()
				.append("update set_acctinfo o,")
				.append("(select b.hosp_id,sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney from set_settlement_detail a")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" where a.settlement_id=:settlementId")
				.append(" group by b.hosp_id) p")
				.append(" set o.available_amt=o.available_amt-p.summoney,")
				.append(" o.pending_amt= o.pending_amt-p.summoney")
				.append(" where o.org_id=p.hosp_id");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("settlementId", settlementId);
		dao.executeSql(sql.toString(), params);
	}

	/**
	 * 
	 * @category 一级结算单不通过将扣减账户的可用余额与待结算金额回加
	 * @author 邱磊
	 * @date 2015年7月25日 上午9:46:14
	 * @param settlementId
	 */
	private void addAcctinfoMoney(String settlementId) {
		StringBuffer sql = new StringBuffer()
				.append("update set_acctinfo o,")
				.append("(select b.hosp_id,sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney from set_settlement_detail a")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" where a.settlement_id=:settlementId")
				.append(" group by b.hosp_id) p")
				.append(" set o.available_amt=o.available_amt+p.summoney,")
				.append(" o.pending_amt= o.pending_amt+p.summoney, ")
				.append(" o.available_amt_upd_dttm= sysdate(), ")
				.append(" o.pending_amt_upd_dttm= sysdate() ")
				.append(" where o.org_id=p.hosp_id");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("settlementId", settlementId);
		dao.executeSql(sql.toString(), params);
	}

	public Pagination getHospSettleList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				" select ss.settlement_id as settlementId, ss.settlement_code as settlementCode, settlement_name as settlementName, ")
				.append(" shb.hea_bur_code as heaBurCode, shb.hea_bur_name as heaBurName, da.ar_shortname as areaName, ss.total_orders as totalOrders, ")
				.append(" ss.total_amount as totalAmount, ss.create_datetime as createDatetime, ss.create_user as createUser, su.name as submitUser, ")
				.append(" ss.submit_datetime as submitDatetime, ss.audit_datetime as auditDatetime, su2.name as auditUser, ")
				.append(" ss.reaudit_datetime as reauditDatetime, su3.name as reAudituser, ss.status as status ")
				.append(" from set_settlement ss inner join std_healthbureau shb on ss.org_id=shb.hea_bur_id ")
				.append(" left join dic_area da on shb.area_id = da.area_id left join sys_user su on ss.submit_user=su.id ")
				.append(" left join sys_user su2 on ss.audit_user=su2.id  left join sys_user su3 on ss.reaudit_user=su3.id where ss.settlement_type='2' ")
				.append(" and ss.status <> '" + Constants.SETTLE_STATUS_NEW
						+ "' ");

		if (StringUtils.isNotBlank(page.getConditions().get("heaBurName"))) {
			sql.append(" and shb.hea_bur_name like :heaBurName ");
			page.getConditions().put("heaBurName",
					"%" + page.getConditions().get("heaBurName") + "%");
		}

		if (StringUtils.isNotBlank(page.getConditions().get("status"))) {
			sql.append(" and ss.status = :status ");
		}

		if (StringUtils.isNotBlank(page.getConditions().get("areaId"))) {
			sql.append(" and shb.area_id like :areaId");
			page.getConditions().put("areaId",
					"%" + page.getConditions().get("areaId") + "%");
		}

		if (StringUtils.isNotBlank(page.getConditions().get(
				"submitStartDatetime"))) {
			sql.append(" and ss.submit_datetime >= :submitStartDatetime ");
			page.getConditions().put(
					"submitStartDatetime",
					page.getConditions().get("submitStartDatetime")
							+ " 00:00:00");
		}

		if (StringUtils.isNotBlank(page.getConditions()
				.get("submitEndDatetime"))) {
			sql.append(" and ss.submit_datetime <= :submitEndDatetime ");
			page.getConditions()
					.put("submitEndDatetime",
							page.getConditions().get("submitEndDatetime")
									+ " 23:59:59");
		}

		if (StringUtils.isNotBlank(page.getConditions().get(
				"auditStartDatetime"))) {
			sql.append(" and ss.audit_datetime >= :auditStartDatetime ");
			page.getConditions().put(
					"auditStartDatetime",
					page.getConditions().get("auditStartDatetime")
							+ " 00:00:00");
		}

		if (StringUtils
				.isNotBlank(page.getConditions().get("auditEndDatetime"))) {
			sql.append(" and ss.audit_datetime <= :auditEndDatetime ");
			page.getConditions().put("auditEndDatetime",
					page.getConditions().get("auditEndDatetime") + " 23:59:59");
		}

		if (StringUtils.isNotBlank(page.getConditions().get(
				"reauditStartDatetime"))) {
			sql.append(" and ss.reaudit_datetime >= :reauditStartDatetime ");
			page.getConditions().put(
					"reauditStartDatetime",
					page.getConditions().get("reauditStartDatetime")
							+ " 00:00:00");
		}

		if (StringUtils.isNotBlank(page.getConditions().get(
				"reauditEndDatetime"))) {
			sql.append(" and ss.reaudit_datetime <= :reauditEndDatetime ");
			page.getConditions().put(
					"reauditEndDatetime",
					page.getConditions().get("reauditEndDatetime")
							+ " 23:59:59");
		}
		sql.append(" order by ss.reaudit_datetime desc ");

		return dao
				.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	public Pagination hospSettleAuditList(Pagination page) {
		String areaId = page.getConditions().get("areaId");
		StringBuffer sql = new StringBuffer();
		sql.append(
				" select ss.settlement_id as settlementId, ss.settlement_code as settlementCode, settlement_name as settlementName, ")
				.append(" shb.hea_bur_code as heaBurCode, shb.hea_bur_name as heaBurName, da.ar_shortname as areaName, ss.total_orders as totalOrders, ")
				.append(" ss.total_amount as totalAmount, ss.create_datetime as createDatetime, ss.create_user as createUser, su.name as submitUser, ")
				.append(" DATE_FORMAT(ss.submit_datetime, '%Y-%m-%d %H:%i:%S') as submitDatetime, DATE_FORMAT(ss.audit_datetime, '%Y-%m-%d %H:%i:%S') as auditDatetime, su2.name as auditUser, ")
				.append(" DATE_FORMAT(ss.reaudit_datetime, '%Y-%m-%d %H:%i:%S') as reauditDatetime, su3.name as reAudituser, ss.status as status ")
				.append(" from set_settlement ss inner join std_healthbureau shb on ss.org_id=shb.hea_bur_id ")
				.append(" left join dic_area da on shb.area_id = da.area_id left join sys_user su on ss.submit_user=su.id ")
				.append(" left join sys_user su2 on ss.audit_user=su2.id   left join sys_user su3 on ss.reaudit_user=su3.id ")
				.append(" where ss.status = :status and ss.settlement_type='2'");
		if (StringUtils.isNotBlank(areaId)) {
			sql.append(" and da.area_id like :areaId ");
			page.getConditions().put("areaId",
					page.getConditions().get("areaId") + "%");
		}
		if (StringUtils.isNotBlank(page.getConditions().get("heaBurName"))) {
			sql.append(" and shb.hea_bur_name like :heaBurName");
			page.getConditions().put("heaBurName",
					"%" + page.getConditions().get("heaBurName") + "%");
		}
		if (StringUtils.isNotBlank(page.getConditions().get(
				"submitStartDatetime"))) {
			sql.append(" and ss.submit_datetime >= :submitStartDatetime ");
			page.getConditions().put(
					"submitStartDatetime",
					page.getConditions().get("submitStartDatetime")
							+ " 00:00:00");
		}
		if (StringUtils.isNotBlank(page.getConditions()
				.get("submitEndDatetime"))) {
			sql.append(" and ss.submit_datetime <= :submitEndDatetime ");
			page.getConditions()
					.put("submitEndDatetime",
							page.getConditions().get("submitEndDatetime")
									+ " 23:59:59");
		}

		if (StringUtils.isNotBlank(page.getConditions().get(
				"auditStartDatetime"))) {
			sql.append(" and ss.audit_datetime >= :auditStartDatetime ");
			page.getConditions().put(
					"auditStartDatetime",
					page.getConditions().get("auditStartDatetime")
							+ " 00:00:00");
		}
		if (StringUtils
				.isNotBlank(page.getConditions().get("auditEndDatetime"))) {
			sql.append(" and ss.audit_datetime <= :auditEndDatetime ");
			page.getConditions().put("auditEndDatetime",
					page.getConditions().get("auditEndDatetime") + " 23:59:59");
		}
		sql.append(" order by submitDatetime ");
		return dao
				.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	public Pagination getChildSettlementList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select ss.settlement_id as settlementId, ss.settlement_code as settlementCode, settlement_name as settlementName, ")
				.append("shb.hosp_code as hospCode, shb.hosp_name as hospName, da.area_name as areaName, ss.total_orders as totalOrders, ")
				.append("ss.total_amount as totalAmount, ss.create_datetime as createDatetime, ss.create_user as createUser, su.name as submitUser, ")
				.append("ss.submit_datetime as submitDatetime, ss.audit_datetime as auditDatetime, su2.name as auditUser, ")
				.append("ss.reaudit_datetime as reauditDatetime, su3.name as reAudituser, ss.status as status ")
				.append("from set_settlement ss inner join std_hospinfo shb on ss.org_id=shb.hosp_id left join dic_area da on shb.area_id = da.area_id ")
				.append("left join sys_user su on ss.submit_user=su.id left join sys_user su2 on ss.audit_user=su2.id  ")
				.append(" left join sys_user su3 on ss.reaudit_user=su3.id where ss.settlement_id in ")
				.append("(select ssd.bank_acct_trans_info_id from set_settlement sst inner join set_settlement_detail ssd ")
				.append(" on sst.settlement_id = ssd.settlement_id where sst.settlement_id= :settlementId)");

		return dao
				.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	public Pagination getSetSettletdetailinfoList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				" select ssdi.trans_id AS transId, b.factor as factor,b.form_name as formName,b.drug_code as drugCode,b.material_name as materialName,b.qualityspec_name as qualityspecName,b.prod_compname as catCompName,b.spec_name as specName,b.unit_name as unitName, shi.hosp_code as hospCode, ")
				.append(" shi.hosp_name as hospName, ssdi.pro_datetime as proDatetime, ssdi.pro_price as proPrice,")
				.append(" ssdi.pro_count as proCount, ssdi.recp_datetime as recpDatetime, ssdi.recp_count as recpCount, ")
				.append(" ssdi.settle_sch_beg_time as settleSchBegTime, ssdi.settle_sch_end_time as settleSchEndTime, ")
				.append(" sci.comp_name as compName ")
				.append(" from set_settletdetailinfo ssdi ")
			//	.append(" inner join view_drug vg on ssdi.drug_id = vg.drug_id                            ")
				.append(" inner join base_goods_item b on b.goods_code = ssdi.goods_code ")
				.append(" inner join std_hospinfo shi on ssdi.hosp_id = shi.hosp_id                                                                            ")
				.append(" inner join std_compinfo sci on ssdi.comp_id = sci.comp_id                                                                            ")
				.append(" where ssdi.trans_id in                                                                                                               ")
				.append(" ( select ssd.bank_acct_trans_info_id from set_settlement_detail ssd where ssd.settlement_id = :settlementId) ");

		return dao
				.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	/**
	 * 
	 * @category 获取初审核查看页面数据（核算中心，采购中心）
	 * @author 邱磊
	 * @date 2015年7月23日 上午11:03:49
	 * @param page
	 * @return
	 */
	public Pagination getAuditSettlementSeeDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select settlement_id as settlementId,a.settlement_code as settlementCode,a.settlement_name as settlementName,")
				.append("c.hosp_code as hospCode,c.hosp_name as hospName,e.area_name as areaName,a.total_orders as totalOrders,")
				.append("a.total_amount as totalAmount,DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,f.name as createUser,d.name as submitUser,")
				.append("DATE_FORMAT(a.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,a.status,DATE_FORMAT(a.audit_datetime,'%Y-%m-%d %H:%i:%S') as auditDatetime,")
				.append("g.name as auditUser,DATE_FORMAT(a.reaudit_datetime,'%Y-%m-%d %H:%i:%S') as reauditDatetime,h.name as reauditUser")
				.append(" from set_settlement a")
				.append(" left join set_relationship b on b.org_id=a.org_id")
				.append(" left join std_hospinfo c on c.hosp_id=a.org_id")
				.append(" left join sys_user d on a.submit_user=d.id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join sys_user f on f.id=a.create_user")
				.append(" left join sys_user g on g.id=a.audit_user")
				.append(" left join sys_user h on h.id=a.reaudit_user")
				.append(" where b.father_org_id=:orgId and a.settlement_type=1 and a.status>1");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orgId", page.getConditions().get("orgId"));
		SetSettlementForm form = (SetSettlementForm) page.getForm();
		if (StringUtils.isNotBlank(form.getBeginDate())) {
			sql.append(" and DATE_FORMAT(a.submit_datetime,'%Y-%m-%d')>=:beginDate");
			params.put("beginDate", form.getBeginDate());
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and DATE_FORMAT(a.submit_datetime,'%Y-%m-%d')<=:endDate");
			params.put("endDate", form.getEndDate());
		}
		if (StringUtils.isNotBlank(form.getStatus())) {
			sql.append(" and a.status=:status");
			params.put("status", form.getStatus());
		}
		if (StringUtils.isNotBlank(form.getHospCode())) {
			sql.append(" and c.hosp_code=:hospCode");
			params.put("getHospCode", form.getHospCode());
		}
		if (StringUtils.isNotBlank(form.getAreaCode())) {
			sql.append(" and e.area_id=:areaCode");
			params.put("areaCode", form.getAreaCode());
		}
		if (StringUtils.isNotBlank(form.getAreaFatherCode())) {
			sql.append(" and e.father_id=:areaFatherCode");
			params.put("areaFatherCode", form.getAreaFatherCode());
		}
		if (StringUtils.isNotBlank(form.getHospName())) {
			sql.append(" and c.hosp_name like :hospName");
			params.put("hospName", "%" + form.getHospName().trim() + "%");
		}
		sql.append(" order by a.reaudit_datetime desc");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取复审核查看页面数据（核算中心，采购中心）
	 * @author 邱磊
	 * @date 2015年7月23日 上午11:03:49
	 * @param page
	 * @return
	 */
	public Pagination getRAuditSettlementSeeDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select settlement_id as settlementId,a.settlement_code as settlementCode,a.settlement_name as settlementName,")
				.append("c.hosp_code as hospCode,c.hosp_name as hospName,e.area_name as areaName,a.total_orders as totalOrders,")
				.append("a.total_amount as totalAmount,DATE_FORMAT(a.create_datetime,'%Y-%m-%d %H:%i:%S') as createDatetime,f.name as createUser,d.name as submitUser,")
				.append("DATE_FORMAT(a.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,a.status,DATE_FORMAT(a.audit_datetime,'%Y-%m-%d %H:%i:%S') as auditDatetime,")
				.append("g.name as auditUser,DATE_FORMAT(a.reaudit_datetime,'%Y-%m-%d %H:%i:%S') as reauditDatetime,h.name as reauditUser")
				.append(" from set_settlement a")
				.append(" left join set_relationship b on b.org_id=a.org_id")
				.append(" left join std_hospinfo c on c.hosp_id=a.org_id")
				.append(" left join sys_user d on a.submit_user=d.id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join sys_user f on f.id=a.create_user")
				.append(" left join sys_user g on g.id=a.audit_user")
				.append(" left join sys_user h on h.id=a.reaudit_user")
				.append(" where b.father_org_id=:orgId and a.settlement_type=1 and a.status>3");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orgId", page.getConditions().get("orgId"));
		SetSettlementForm form = (SetSettlementForm) page.getForm();
		if (StringUtils.isNotBlank(form.getBeginDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')>=:beginDate");
			params.put("beginDate", form.getBeginDate());
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and DATE_FORMAT(a.create_datetime,'%Y-%m-%d')<=:endDate");
			params.put("endDate", form.getEndDate());
		}
		if (StringUtils.isNotBlank(form.getStatus())) {
			sql.append(" and a.status=:status");
			params.put("status", form.getStatus());
		}
		if (StringUtils.isNotBlank(form.getHospCode())) {
			sql.append(" and c.hosp_code=:hospCode");
			params.put("getHospCode", form.getHospCode());
		}
		if (StringUtils.isNotBlank(form.getAreaCode())) {
			sql.append(" and e.area_id=:areaCode");
			params.put("areaCode", form.getAreaCode());
		}
		if (StringUtils.isNotBlank(form.getAreaFatherCode())) {
			sql.append(" and e.father_id=:areaFatherCode");
			params.put("areaFatherCode", form.getAreaFatherCode());
		}
		if (StringUtils.isNotBlank(form.getHospName())) {
			sql.append(" and c.hosp_name like :hospName");
			params.put("hospName", "%" + form.getHospName().trim() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	public Pagination auditPass(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("update set_settlement set status = :status, audit_remark= :auditRemark, audit_user= :auditUser, ")
				.append("audit_datetime= :auditDatetime where settlement_id = :settlementId");

		dao.executeSql(sql.toString(), page.getConditions());
		return page;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Pagination auditUnPass(Pagination page) {

		StringBuffer sql = new StringBuffer()
				.append("update set_settlement set status = :status, audit_remark= :auditRemark, audit_user= :auditUser, ")
				.append("audit_datetime= :auditDatetime where settlement_id = :settlementId");
		dao.executeSql(sql.toString(), page.getConditions());

		HashMap hm = new HashMap<String, String>();
		hm.put("settlementId", page.getConditions().get("settlementId"));
		hm.put("updUser", page.getConditions().get("auditUser"));

		sql = new StringBuffer()
				.append("UPDATE set_acctinfo a, ( SELECT total_amount, org_id FROM set_settlement WHERE settlement_id = :settlementId ) b SET a.available_amt = a.available_amt + b.total_amount, a.pending_amt = a.pending_amt + b.total_amount, a.available_amt_upd_dttm = SYSDATE(), a.pending_amt_upd_dttm = SYSDATE(), a.upd_datetime = SYSDATE(), a.upd_user = :updUser WHERE a.org_id = b.org_id ");
		dao.executeSql(sql.toString(), hm);

		hm = new HashMap<String, String>();
		hm.put("settlementId", page.getConditions().get("settlementId"));
		hm.put("updDatetime", page.getConditions().get("auditDatetime"));
		hm.put("updUser", page.getConditions().get("auditDatetime"));
		sql = new StringBuffer()
				.append(" update set_settletdetailinfo set is_in_settle_slip = '0', upd_datetime = :updDatetime, upd_user = :updUser ")
				.append(" where trans_id in (select bank_acct_trans_info_id from set_settlement_detail ssdl where ssdl.settlement_id in ")
				.append(" (select ssd.bank_acct_trans_info_id from set_settlement ss ")
				.append(" inner join set_settlement_detail ssd on ss.settlement_id = ssd.settlement_id ")
				.append("  where ss.settlement_id = :settlementId))");

		dao.executeSql(sql.toString(), hm);
		return page;
	}

	public Pagination reAuditPass(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("update set_settlement set status = :status, reaudit_remark= :reauditRemark, reaudit_user= :reauditUser, ")
				.append("reaudit_datetime= :reauditDatetime, pay_status = 1 where settlement_id = :settlementId");

		dao.executeSql(sql.toString(), page.getConditions());
		return page;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Pagination reAuditUnPass(Pagination page) {

		StringBuffer sql = new StringBuffer()
				.append("update set_settlement set status = :status, reaudit_remark= :reauditRemark, reaudit_user= :reauditUser, ")
				.append("reaudit_datetime= :reauditDatetime where settlement_id = :settlementId");
		dao.executeSql(sql.toString(), page.getConditions());

		HashMap hm = new HashMap<String, String>();
		hm.put("settlementId", page.getConditions().get("settlementId"));
		hm.put("updUser", page.getConditions().get("auditUser"));

		sql = new StringBuffer()
				.append("UPDATE set_acctinfo a, ( SELECT total_amount, org_id FROM set_settlement WHERE settlement_id = :settlementId ) b SET a.available_amt = a.available_amt + b.total_amount, a.pending_amt = a.pending_amt + b.total_amount, a.available_amt_upd_dttm = SYSDATE(), a.pending_amt_upd_dttm = SYSDATE(), a.upd_datetime = SYSDATE(), a.upd_user = :updUser WHERE a.org_id = b.org_id ");
		dao.executeSql(sql.toString(), hm);

		hm = new HashMap<String, String>();
		hm.put("settlementId", page.getConditions().get("settlementId"));
		hm.put("updDatetime", page.getConditions().get("reauditDatetime"));
		hm.put("updUser", page.getConditions().get("reauditUser"));
		sql = new StringBuffer()
				.append(" update set_settletdetailinfo set is_in_settle_slip = '0', upd_datetime = :updDatetime, upd_user = :updUser ")
				.append(" where trans_id in (select bank_acct_trans_info_id from set_settlement_detail ssdl where ssdl.settlement_id in ")
				.append(" (select ssd.bank_acct_trans_info_id from set_settlement ss ")
				.append(" inner join set_settlement_detail ssd on ss.settlement_id = ssd.settlement_id ")
				.append("  where ss.settlement_id = :settlementId))");

		dao.executeSql(sql.toString(), hm);
		return page;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.hsnn.medstgmini.settlement.dao.SettlementFirstDao#
	 * getSettlementDetailInfoBysetSettlementId(java.lang.String)
	 */
	@Override
	public Object getSettlementDetailInfoBysetSettlementId(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ss.bank_acct_trans_type as bankAcctTransType,")
				.append(" date_format(ss.pro_datetime,'%Y-%m-%d %H:%i:%S') as proDatetime,")
				.append("ss.pro_price as proPrice,")
				.append("ss.pro_count as proCount,")
				.append("sh.hosp_name as hospName,")
				.append("sc.comp_name as compName,")
				.append(" b.drug_code as drugCode, ")
				.append(" b.factor as factor, ")
				.append(" b.form_name as formName, ")
				.append(" b.material_name as materialName, ")
				.append(" b.qualityspec_name as qualityspecName, ")
				.append(" b.prod_compname as catCompName, ")
				.append(" b.spec_name as specName, ")
				.append(" b.unit_name as unitName ")
				/*.append("vg.drug_code as drugCode,")
				.append("vg.drug_name as drugName,")
				.append("vg.form_name as formName,")
				.append("vg.spec_name as specName,")
				.append("vg.factor,")
				.append("vg.unit,")
				.append("vg.material_name as materialName,")
				.append(" vg.comp_name as proCompName ")*/
				.append("from set_settletdetailinfo ss ")
				.append(" inner join base_goods_item b on b.goods_code = ss.goods_code ")
				// .append(" inner join  view_drug vg on vg.drug_id = ss.drug_id")
				.append(" inner join std_hospinfo sh on sh.hosp_id = ss.hosp_id")
				.append(" inner join std_compinfo sc on sc.comp_id =  ss.comp_id ")
				.append(" where  ss.trans_id = :transId");

		return dao.getSqlMapUniqueResult(sql.toString(), page.getConditions());
	}

	/**
	 * @category 医疗机构查看结算单
	 * @author 应晓川
	 * @date 2015年8月5日09:22:22
	 * @return
	 */
	@Override
	public Pagination getSettlementDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" SELECT a.settlement_id as settlementId, ")
				.append("       a.settlement_code as settlementCode, ")
				.append("       a.settlement_name as settlementName, ")
				.append("       a.total_orders as totalOrders, ")
				.append("       a.total_amount as totalAmount, ")
				.append("       DATE_FORMAT(a.create_datetime, ")
				.append("       '%Y-%m-%d %H:%i:%S') as createDatetime, ")
				.append("       c.name as createUser, ")
				.append("       a.status, ")
				.append("       a.remarks, ")
				.append("       d.name as submitUser, ")
				.append("       DATE_FORMAT(a.submit_datetime, ")
				.append("       '%Y-%m-%d %H:%i:%S') as submitDatetime, ")
				.append("        DATE_FORMAT(a.audit_datetime,'%Y-%m-%d %H:%i:%S')AS auditDatetime, ")
				.append("       a.audit_remark AS auditRemark, ")
				.append("       e.name as auditUser ")
				.append("   from set_settlement a  ")
				.append("   LEFT JOIN sys_user c  on c.id=a.create_user  ")
				.append("   left join sys_user d  on d.id=a.submit_user  ")
				.append("   left join sys_user e  on e.id=a.audit_user  ")
				.append("   where a.org_id= :hospId ");

		String beginDate = page.getConditions().get("beginDate");
		String endDate = page.getConditions().get("endDate");
		String status = page.getConditions().get("status");
		if (StringUtils.isNotBlank(beginDate)) {
			sql.append(" and a.create_datetime >=:beginDate");
			page.getConditions().put("beginDate", beginDate + " 00:00:00");
		}
		if (StringUtils.isNotBlank(endDate)) {
			sql.append(" and a.create_datetime<=:endDate");
			page.getConditions().put("endDate", endDate + " 23:59:59");
		}
		if (StringUtils.isNotBlank(status)) {
			sql.append(" AND a.status =:status ");
		}
		sql.append("  order by a.create_datetime desc ");

		return dao
				.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	/**
	 * @category 检测名称是否存在
	 * @date 2015年8月11日
	 * @param settlement
	 * @return
	 */
	public boolean checkSameName(SetSettlement settlement) {
		if (settlement == null) {
			return false;
		}
		String settlementId = settlement.getSettlementId();
		String settlementName = settlement.getSettlementName();
		if (StringUtils.isNotBlank(settlementName)) {
			Session session = getSession();
			Criteria criteria = session.createCriteria(SetSettlement.class);
			criteria.add(Restrictions.eq("settlementName", settlementName));
			if (StringUtils.isNotBlank(settlementId)) {
				criteria.add(Restrictions.not(Restrictions.eq("settlementId",
						settlementId)));
			}
			int size = criteria.list().size();
			if (size > 0) {
				return true;
			}
		}
		return false;
	}

}